package dao;

import entity.Playlist;
import entity.Song;
import util.DatabaseUtil;
import java.sql.*;
import java.util.*;

public class PlaylistDao {
    
    // 根据ID查询歌单
    public Playlist findById(Long id) throws SQLException {
        String sql = "SELECT * FROM playlists WHERE playlist_id = ?";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setLong(1, id);
            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                return mapResultSetToPlaylist(rs);
            }
        }
        return null;
    }
    
    // 获取用户创建的歌单
    public List<Playlist> findByCreatorId(Long userId) throws SQLException {
        List<Playlist> playlists = new ArrayList<>();
        String sql = "SELECT * FROM playlists WHERE creator_id = ?";
        
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setLong(1, userId);
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                playlists.add(mapResultSetToPlaylist(rs));
            }
        }
        return playlists;
    }
    
    // 获取用户收藏的歌单
    public List<Playlist> findFavoritesByUserId(Long userId) throws SQLException {
        List<Playlist> playlists = new ArrayList<>();
        String sql = 
            "SELECT p.* FROM playlists p " +
            "JOIN playlist_favorites pf ON p.playlist_id = pf.playlist_id " +
            "WHERE pf.user_id = ?";
            
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setLong(1, userId);
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                playlists.add(mapResultSetToPlaylist(rs));
            }
        }
        return playlists;
    }
    
    // 获取歌单中的歌曲
    public List<Song> findSongsByPlaylistId(Long playlistId) throws SQLException {
        List<Song> songs = new ArrayList<>();
        String sql = 
            "SELECT s.* FROM songs s " +
            "JOIN playlist_songs ps ON s.song_id = ps.song_id " +
            "WHERE ps.playlist_id = ? " +
            "ORDER BY ps.song_order ASC";
            
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setLong(1, playlistId);
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                songs.add(new SongDao().mapResultSetToSong(rs));
            }
        }
        return songs;
    }
    
    // 创建歌单
    public Playlist create(Playlist playlist) throws SQLException {
        String sql = "INSERT INTO playlists (name, description, cover_file_path, creator_id) VALUES (?, ?, ?, ?)";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
            stmt.setString(1, playlist.getName());
            stmt.setString(2, playlist.getDescription());
            stmt.setString(3, playlist.getCoverFilePath());
            stmt.setLong(4, playlist.getCreatorId());
            
            stmt.executeUpdate();
            ResultSet rs = stmt.getGeneratedKeys();
            if (rs.next()) {
                playlist.setPlaylistId(rs.getLong(1));
            }
            return playlist;
        }
    }
    
    // 更新歌单
    public void update(Playlist playlist) throws SQLException {
        String sql = "UPDATE playlists SET name = ?, description = ?, cover_file_path = ? WHERE playlist_id = ?";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, playlist.getName());
            stmt.setString(2, playlist.getDescription());
            stmt.setString(3, playlist.getCoverFilePath());
            stmt.setLong(4, playlist.getPlaylistId());
            stmt.executeUpdate();
        }
    }
    
    // 删除歌单
    public void delete(Long playlistId) throws SQLException {
        Connection conn = DatabaseUtil.getConnection();
        conn.setAutoCommit(false);
        try {
            // 删除歌单中的歌曲关联
            String deleteSongsSql = "DELETE FROM playlist_songs WHERE playlist_id = ?";
            try (PreparedStatement stmt = conn.prepareStatement(deleteSongsSql)) {
                stmt.setLong(1, playlistId);
                stmt.executeUpdate();
            }
            
            // 删除歌单收藏关联
            String deleteFavoritesSql = "DELETE FROM playlist_favorites WHERE playlist_id = ?";
            try (PreparedStatement stmt = conn.prepareStatement(deleteFavoritesSql)) {
                stmt.setLong(1, playlistId);
                stmt.executeUpdate();
            }
            
            // 删除歌单
            String deletePlaylistSql = "DELETE FROM playlists WHERE playlist_id = ?";
            try (PreparedStatement stmt = conn.prepareStatement(deletePlaylistSql)) {
                stmt.setLong(1, playlistId);
                stmt.executeUpdate();
            }
            
            conn.commit();
        } catch (Exception e) {
            conn.rollback();
            throw e;
        } finally {
            conn.setAutoCommit(true);
        }
    }
    
    // 添加歌曲到歌单
    public void addSongs(Long playlistId, List<Long> songIds) throws SQLException {
        Connection conn = DatabaseUtil.getConnection();
        conn.setAutoCommit(false);
        try {
            // 获取当前最大序号
            int maxOrder = 0;
            String maxOrderSql = "SELECT COALESCE(MAX(song_order), 0) as max_order FROM playlist_songs WHERE playlist_id = ?";
            try (PreparedStatement stmt = conn.prepareStatement(maxOrderSql)) {
                stmt.setLong(1, playlistId);
                ResultSet rs = stmt.executeQuery();
                if (rs.next()) {
                    maxOrder = rs.getInt("max_order");
                }
            }
            
            // 批量插入歌曲
            String insertSql = "INSERT IGNORE INTO playlist_songs (playlist_id, song_id, song_order) VALUES (?, ?, ?)";
            try (PreparedStatement stmt = conn.prepareStatement(insertSql)) {
                for (Long songId : songIds) {
                    stmt.setLong(1, playlistId);
                    stmt.setLong(2, songId);
                    stmt.setInt(3, ++maxOrder);
                    stmt.addBatch();
                }
                stmt.executeBatch();
            }
            
            conn.commit();
        } catch (Exception e) {
            conn.rollback();
            throw e;
        } finally {
            conn.setAutoCommit(true);
        }
    }
    
    // 从歌单中移除歌曲
    public void removeSongs(Long playlistId, List<Long> songIds) throws SQLException {
        Connection conn = DatabaseUtil.getConnection();
        conn.setAutoCommit(false);
        try {
            String deleteSql = "DELETE FROM playlist_songs WHERE playlist_id = ? AND song_id = ?";
            try (PreparedStatement stmt = conn.prepareStatement(deleteSql)) {
                for (Long songId : songIds) {
                    stmt.setLong(1, playlistId);
                    stmt.setLong(2, songId);
                    stmt.addBatch();
                }
                stmt.executeBatch();
            }
            
            // 重新排序剩余歌曲
            String reorderSql = 
                "UPDATE playlist_songs " +
                "SET song_order = (@row_number := @row_number + 1) " +
                "WHERE playlist_id = ? " +
                "ORDER BY song_order ASC";
                
            try (PreparedStatement stmt = conn.prepareStatement("SET @row_number = 0")) {
                stmt.executeUpdate();
            }
            
            try (PreparedStatement stmt = conn.prepareStatement(reorderSql)) {
                stmt.setLong(1, playlistId);
                stmt.executeUpdate();
            }
            
            conn.commit();
        } catch (Exception e) {
            conn.rollback();
            throw e;
        } finally {
            conn.setAutoCommit(true);
        }
    }
    
    // 收藏/取消收藏歌单
    public void toggleFavorite(Long playlistId, Long userId, boolean favorite) throws SQLException {
        String sql = favorite ? 
            "INSERT IGNORE INTO playlist_favorites (playlist_id, user_id) VALUES (?, ?)" :
            "DELETE FROM playlist_favorites WHERE playlist_id = ? AND user_id = ?";
            
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setLong(1, playlistId);
            stmt.setLong(2, userId);
            stmt.executeUpdate();
        }
    }
    
    // 检查是否已收藏
    public boolean isFavorited(Long playlistId, Long userId) throws SQLException {
        String sql = "SELECT 1 FROM playlist_favorites WHERE playlist_id = ? AND user_id = ?";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setLong(1, playlistId);
            stmt.setLong(2, userId);
            ResultSet rs = stmt.executeQuery();
            return rs.next();
        }
    }
    
    // 获取热门歌单
    public List<Playlist> findHotPlaylists(int limit) throws SQLException {
        List<Playlist> playlists = new ArrayList<>();
        String sql = "SELECT p.*, COUNT(pf.user_id) as favorite_count " +
            "FROM playlists p " +
            "LEFT JOIN playlist_favorites pf ON p.playlist_id = pf.playlist_id " +
            "GROUP BY p.playlist_id " +
            "ORDER BY favorite_count DESC, p.play_count DESC, p.created_at DESC " +
            "LIMIT ?";
        
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, limit);
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                Playlist playlist = mapResultSetToPlaylist(rs);
                playlist.setFavoriteCount(rs.getInt("favorite_count"));
                playlists.add(playlist);
            }
        }
        return playlists;
    }
    
    // 检查多个歌单是否被用户收藏
    public Map<Long, Boolean> checkPlaylistsFavoriteStatus(List<Long> playlistIds, Long userId) throws SQLException {
        Map<Long, Boolean> favoriteStatus = new HashMap<>();
        if (playlistIds.isEmpty()) return favoriteStatus;

        String sql = "SELECT playlist_id FROM playlist_favorites WHERE user_id = ? AND playlist_id IN (" + 
            String.join(",", Collections.nCopies(playlistIds.size(), "?")) + ")";
        
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setLong(1, userId);
            for (int i = 0; i < playlistIds.size(); i++) {
                stmt.setLong(i + 2, playlistIds.get(i));
            }
            
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                favoriteStatus.put(rs.getLong("playlist_id"), true);
            }
            
            // 设置未收藏的歌单状态
            playlistIds.forEach(id -> favoriteStatus.putIfAbsent(id, false));
        }
        return favoriteStatus;
    }
    
    // ResultSet映射到实体
    private Playlist mapResultSetToPlaylist(ResultSet rs) throws SQLException {
        Playlist playlist = new Playlist();
        playlist.setPlaylistId(rs.getLong("playlist_id"));
        playlist.setName(rs.getString("name"));
        playlist.setDescription(rs.getString("description"));
        playlist.setCoverFilePath(rs.getString("cover_file_path"));
        playlist.setCreatorId(rs.getLong("creator_id"));
        playlist.setPlayCount(rs.getInt("play_count"));
        playlist.setCreatedAt(rs.getTimestamp("created_at"));
        return playlist;
    }
    
    // 增加播放次数
    public void incrementPlayCount(Long playlistId) throws SQLException {
        String sql = "UPDATE playlists SET play_count = play_count + 1 WHERE playlist_id = ?";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setLong(1, playlistId);
            stmt.executeUpdate();
        }
    }
} 